﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Objects;
using System.IO;

namespace EWDTAssignment.App_Code
{
    public class EntityLogic
    {
        public static List<Products> RetrieveProducts(string category, int type)
        {
            using (eShopEntities context = new eShopEntities())
            {
                int l = BusinessLogic.range(type, -1), h = BusinessLogic.range(type, 1);
                var pQuery = from p in context.Products
                             where (p.Type >= l && p.Type <= h) && p.Category == category
                             select p;

                List<Products> pList = pQuery.ToList();

                context.Dispose();
                return pList;
            }
        }

        public static List<Products> RetrieveProducts(string category, int type, string flag)
        {
            /*using (eShopEntities context = new eShopEntities())
            {
                string queryString =
                    @"SELECT * FROM eShopEntities.Products AS p 
                    WHERE p.Type == @type AND
                    p.Category = @cat AND
                    p.Flag LIKE @flag";

                ObjectQuery<Product> pQuery = new ObjectQuery<Product>(queryString, context);

                pQuery.Parameters.Add(new ObjectParameter("type", type));
                pQuery.Parameters.Add(new ObjectParameter("cat", category));
                pQuery.Parameters.Add(new ObjectParameter("flag", flag));
            }*/

            using (eShopEntities context = new eShopEntities())
            {
                List<Products> pList = null;

                int l = BusinessLogic.range(type, -1), h = BusinessLogic.range(type, 1);

                if (category == "HDD" && flag == "SATA23")
                {
                    var pQuery = from p in context.Products
                                 where (p.Type >= l && p.Type <= h) && p.Category == "HDD" && (p.Flag.Contains("SATA3") || p.Flag.Contains("SATA2"))
                                 select p;
                    pList = pQuery.ToList();
                }
                else
                {
                    var pQuery = from p in context.Products
                                 where (p.Type >= l && p.Type <= h) && p.Category == category && p.Flag.Contains(flag)
                                 select p;
                    /*var pQuery = from p in context.Products
                                 where p.Type == 3 && p.Category == "CPU"
                                     select p;*/
                    pList = pQuery.ToList();
                }

                //List<Products> pList = pQuery.ToList();

                context.Dispose();
                return pList;
            }
        }

        public static Products RetrieveProductByID(int PID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var pQuery = from p in context.Products
                             where p.ProductID == PID
                             select p;

                Products product = pQuery.First();

                context.Dispose();
                return product;
            }

        }

        public static List<Products> RetrieveProductsFromSC(int ProductID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var pQuery = from p in context.Products
                             join sc in context.ShoppingCarts
                             on p.ProductID equals sc.ProductID
                             where sc.ProductID == ProductID
                             select p;

                List<Products> pList = pQuery.ToList();
                return pList;
            }
        }

        public static int RetrieveProductInventory(int ProductID)
        {
            int inventory = 0;

            using (eShopEntities context = new eShopEntities())
            {
                var pQuery = from p in context.Products
                             where p.ProductID == ProductID
                             select p.Quantity;

                inventory = (int)pQuery.First();
                return inventory;
            }
        }

        public static void UpdateProductInventory(int ProductID, int quantity)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var pQuery =
                    from p in context.Products
                    where p.ProductID == ProductID
                    select p;

                foreach (var products in pQuery)
                {
                    products.Quantity -= quantity;
                }
                context.SaveChanges();
            }
        }

        /*public static DataTable RetrieveProductSQL(int type, string category)
        {
            using (eShopEntities context = new eShopEntities())
            {
                string queryString =
                    @"SELECT * FROM eShopEntities.Product AS p WHERE p.Type == @type AND
                   p.Category = @cat";

                ObjectQuery<Product> pQuery = new ObjectQuery<Product>(queryString, context);

                pQuery.Parameters.Add(new ObjectParameter("type", type));
                pQuery.Parameters.Add(new ObjectParameter("cat", category));

                 
            }

        }*/

        public static int CreateSet(Dictionary<string, int> d, int accID)
        {
            int setID = -1;
            try
            {
                using (eShopEntities context = new eShopEntities())
                {
                    var pQuery = from s in context.SetItems
                                 select s.SetID;

                    int[] setIDList = pQuery.ToArray<int>();

                    if (setIDList.Count() != 0)
                    {
                        setID = setIDList.Max();
                        setID++;
                    }
                    else
                    {
                        setID = 1;
                    }
                    context.Dispose();
                }
                

                List<int> itemList = new List<int>();
                Dictionary<string, int> d2 = new Dictionary<string, int>();
                //HashSet<string> exist = new HashSet<string>();

                foreach (KeyValuePair<string, int> kvp in d)
                {
                    string hdd, gfx;

                    for (int i = 1; i < 7; i++)
                    {
                        hdd = "HDD" + i;
                        if (kvp.Key == hdd)
                        {
                            itemList.Add(kvp.Value);
                        }
                    }

                    for (int i = 1; i < 3; i++)
                    {
                        gfx = "GFX" + i;
                        if (kvp.Key == gfx)
                        {
                            itemList.Add(kvp.Value);
                        }
                    }

                    //Creates a new dictionary of unique products
                    if (!d2.ContainsValue(kvp.Value))
                    {
                        d2.Add(kvp.Key, kvp.Value);
                    }

                    /*if(exist.Add(kvp.Value.ToString()))
                    {
                        d2.Add(kvp.Key, kvp.Value);
                    }*/
                }

                Dictionary<int, int> itemQuan = BusinessLogic.SetItemQuantityChk(itemList);

                foreach (KeyValuePair<string, int> kvp in d2)
                {
                    if (kvp.Value != 0)
                    {
                        using (eShopEntities context = new eShopEntities())
                        {
                            //int setID = context.SetItems.OrderByDescending<u => u, setID>;
                            //int setID = context.SetItems.Max<setID 
                            //SetItems s = new SetItems();

                            SetItems set = new SetItems();
                            //Remember to change to Session["AccountID"]
                            int itemQty = 1;
                            if (itemQuan.ContainsKey(kvp.Value))
                            {
                                itemQty = itemQuan[kvp.Value];
                            }

                            set.ProductID = kvp.Value;
                            set.SetID = setID;
                            set.SetItemQty = itemQty;
                            set.AccountID = accID;
                            context.AddToSetItems(set);
                            context.SaveChanges();

                            context.Dispose();
                        }
                    }
                }

                
            }
            catch (Exception e)
            {
                setID = -1;
            }

            return setID;
        }
    

        public static List<int> RetrieveSetList(int accID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var siQuery = from sl in context.SetItems
                              where sl.AccountID == accID
                              select sl.SetID;

                List<int> sList = siQuery.Distinct().ToList();

                context.Dispose();
                return sList;
            }
        }

        
        
        //Retrieves a List of Products by joining SetItem and Product tables
        //to retrieve info of the products which belong to a specific set.
        public static List<Products> RetrieveSetContents(int setID)
        {
            List<Products> pList = new List<Products>();
            if(setID!=0)
            {

                using (eShopEntities context = new eShopEntities())
                {
                    var pQuery = from p in context.Products
                                 join s in context.SetItems
                                 on p.ProductID equals s.ProductID
                                 where s.SetID == setID
                                 select p;

                    pList = pQuery.ToList();

                    context.Dispose();
                }

            } return pList;
        }

        public static int RetrieveSetItemQuantity(Products pro, int setID)
        {
            int quantity = 0;
            if (pro != null)
            {
                using (eShopEntities context = new eShopEntities())
                {
                    var sQuery = from s in context.SetItems
                                 join p in context.Products
                                 on s.ProductID equals p.ProductID
                                 where s.SetID == setID && s.ProductID == pro.ProductID
                                 select s.SetItemQty;

                    quantity = (int) sQuery.First();

                    context.Dispose();
                }

            }
            return quantity;
        }

        public static int RetrieveSetItemAccID(int setID)
        {
            int accID = 0;
            if (setID!=null&&setID!=0)
            {
                using (eShopEntities context = new eShopEntities())
                {
                    var sQuery = from s in context.SetItems
                                 where s.SetID == setID
                                 select s.AccountID;

                    accID = (int)sQuery.First();

                    context.Dispose();
                }

            }
            return accID;
        }

        public static void UpdateSetItemTuple(int setID, int ProductID, int quantity, int origAccID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var sQuery =
                from s in context.SetItems
                where s.SetID == setID && s.ProductID == ProductID
                select s;

                if (sQuery.Count() != 0)
                {
                    if (quantity != 0)
                    {
                        foreach (var setitems in sQuery)
                        {
                            setitems.SetItemQty = quantity;
                        }
                    }
                    else
                    {
                        DeleteSetItemTuple(setID, ProductID);
                    }
                    context.SaveChanges();
                }
                else
                {
                    if (quantity != 0)
                    {
                        InsertSetItemTuple(setID, ProductID, quantity, origAccID);
                    }
                }

                context.Dispose();
            }
        }

        public static void InsertSetItemTuple(int setID, int productID, int quantity, int origAccID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                SetItems set = new SetItems();
                //Remember to change to Session["AccountID"]
                
                set.ProductID = productID;
                set.SetID = setID;
                set.SetItemQty = quantity;
                set.AccountID = origAccID;
                context.AddToSetItems(set);
                context.SaveChanges();

                context.Dispose();
            }
        }

        public static void DeleteSetItemTuple(int setID, int productID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var sQuery =
                from s in context.SetItems
                where s.SetID == setID && s.ProductID == productID
                select s;

                foreach (var del in sQuery)
                {
                    context.SetItems.DeleteObject(del);
                }
                context.SaveChanges();

                context.Dispose();
            }
        }
        
        public static void DeleteSet(int setID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var sQuery =
                from s in context.SetItems
                where s.SetID == setID
                select s;

                foreach (var del in sQuery)
                {
                    context.SetItems.DeleteObject(del);
                }
                context.SaveChanges();

                context.Dispose();
            }
        }

        public static Accounts RetrieveAccountByID(int AccountID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var aQuery = from a in context.Accounts
                             where a.AccountID == AccountID
                             select a;

                Accounts acc = aQuery.First();

                context.Dispose();
                return acc;
            }

        }

        public static void UpdateAddress(int accID, string address, int postalCode)
        {
            using (eShopEntities context = new eShopEntities())
            {
                var aQuery =
                from a in context.Accounts
                where a.AccountID == accID
                select a;

                if (aQuery.Count() != 0)
                {
                    foreach (var acc in aQuery)
                    {
                        acc.Address = address;
                        acc.PostalCode = postalCode;
                    }
                    context.SaveChanges();
                }
                context.Dispose();
            }
        }

        public static void InsertPurchaseOrder(int productID, int quantity, int accID)
        {
            using (eShopEntities context = new eShopEntities())
            {
                PurchaseOrders po = new PurchaseOrders();
                //Remember to change to Session["AccountID"]

                po.OrderDateTime = System.DateTime.Now;
                po.ProductID = productID;
                po.Quantity = quantity;
                po.AccountID = accID;
                context.AddToPurchaseOrders(po);
                context.SaveChanges();

                context.Dispose();
            }
        }

        /*public static DataTable RetrieveProduct(int AccountID, int ProductID, int CartQty)
        {
            DataTable dt = new DataTable();

            using (eShopEntities context = new eShopEntities())
            {
                var pQuery = from p in context.Products
                             join s in context.SetItems
                             on p.ProductID equals s.ProductID
                             where s.SetID == setID
                             select p;
            }
            return dt;

        }*/

        /*public static List<Products> RetrieveProducts(int type, string category)
        {
            //DataTable dt = new DataTable();

            using (eShopEntities context = new eShopEntities())
            {
                int l = low(type), h = high(type);
                var pQuery = from p in context.Products
                             where (p.Type >= l && p.Type <= h) && p.Category == category
                             select p;

                List<Products> pList = pQuery.ToList();

                if (pQuery != null)
                {
                    dt.Columns.Add("ProductID", typeof(int));
                    dt.Columns.Add("ProductName", typeof(string));
                    dt.Columns.Add("Category", typeof(string));
                    dt.Columns.Add("Description", typeof(string));
                    dt.Columns.Add("DiscountRate", typeof(int));
                    dt.Columns.Add("Flag", typeof(string));
                    dt.Columns.Add("Specification", typeof(string));
                    dt.Columns.Add("Type", typeof(int));

                    foreach (var r in pQuery)
                    {
                        DataRow dr = dt.NewRow();
                        dr["ProductID"] = r.ProductID;
                        dr["ProductName"] = r.ProductName;
                        dr["Category"] = r.Category;
                        dr["Description"] = r.Description;
                        dr["Flag"] = r.Flag;
                        dr["Specification"] = r.Specification;
                        dr["Type"] = r.Type;
                        dt.Rows.Add(dr);
                    }
                }
                context.Dispose();
                return pList;
            }


        }

        public static DataTable RetrieveProductSQL(int type, string category)
        {
            using (eShopEntities context = new eShopEntities())
            {
                string queryString =
                    @"SELECT * FROM eShopEntities.Product AS p WHERE p.Type == @type AND
                   p.Category = @cat";

                ObjectQuery<Product> pQuery = new ObjectQuery<Product>(queryString, context);

                pQuery.Parameters.Add(new ObjectParameter("type", type));
                pQuery.Parameters.Add(new ObjectParameter("cat", category));

                 
            }

        }*/


    }
}